package com.mybatis.dao;

import com.mybatis.pojo.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 原生JDBC开发
 */
public class JDBCImpl {

    private static Logger logger = LoggerFactory.getLogger(JDBCImpl.class);

    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/first_db?characterEncoding=utf-8";
    private String username = "root";
    private String password = "123456";


    public List<User> queryUserList() throws Exception {
        logger.debug("====start ok");
        List<User> userList = new ArrayList<User>();

        Class.forName(driver);
        /**
         * 问题一：频繁获取/释放数据库连接，影响数据库和应用性能
         * 解决：数据库连接池技术，C3P0,DRUID（阿里巴巴荣誉出品，号称前无古人后无来者世界最强没有之一）
         */
        Connection connection = DriverManager.getConnection(url, username, password);
        /**
         * 问题二：sql语句硬编码，后期难以维护
         * 解决：若sql语句和java代码分离，比如sql写在配置文件中。Mybatis就是这么干的
         */
        String sql = "select * from user";
        /**
         * 问题三：sql语句where条件和占位符一一对应，后期难以维护
         */
        // String sql1 = "select * from user where username=? and id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // preparedStatement.setInt(1,2);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        /**
         * 问题四：结果集解析麻烦，查询列硬编码
         * 期望：如果单条记录直接返回实体对象，如果多条记录返回实体的集合
         */
        while(resultSet.next()) {
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setSex(resultSet.getString("sex"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setAddress(resultSet.getString("address"));
            userList.add(user);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
        return userList;
    }

}
